﻿
using DataAccess.DTO;
using DataAccess.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYD.DataAccess;
using XYD.Framework.Data;
using XYD.Framework.Data.Dapper;



namespace DataAccess.DAL
{


    public class OrdersDAL : BaseDAL
    {
        
      

        /// <summary>
        /// 创建推广者订单表 订单统计表
        /// </summary>
        /// <param name="promoter_id"></param>
        /// <returns></returns>
        public bool CreateOrderTable(int promoter_id)
        {
            string Sql = string.Format(@"CREATE TABLE Orders_{0}(
                                    [Id] [int] IDENTITY(1,1) NOT NULL,
	                                [CreateDate] [datetime] NULL,
	                                [ClickDate] [datetime] NULL,
	                                [ProductDes] [nvarchar](200) NULL,
	                                [ProductID] [bigint] NULL,
	                                [ShopName] [nvarchar](50) NULL,
	                                [ProductNum] [int] NULL,
	                                [ProductPrice] [decimal](18, 2) NULL,
	                                [OrderStateDes] [nvarchar](50) NULL,
	                                [OrderType] [nvarchar](50) NULL,
	                                [IncomeRatio] [nvarchar](50) NULL,
	                                [DividedIntoRatio] [nvarchar](50) NULL,
	                                [PayPrice] [decimal](18, 2) NULL,
	                                [EffectPrediction] [decimal](18, 2) NULL,
	                                [SettlementPrice] [decimal](18, 2) NULL,
	                                [EstimatedRevenue] [decimal](18, 2) NULL,
	                                [SettlementDate] [datetime] NULL,
	                                [CommissionRate] [nvarchar](50) NULL,
	                                [CommissionPrice] [decimal](18, 2) NULL,
	                                [SubsidyRate] [nvarchar](50) NULL,
	                                [SubsidyPrice] [decimal](18, 2) NULL,
	                                [OrderID] [nvarchar](50) NOT NULL,
                                    [ClassName] [nvarchar](50) NOT NULL,
	                                [SourceMediaName] [nvarchar](50) NULL,
	                                [AdvID] [nvarchar](50) NULL,
	                                [AdvName] [nvarchar](50) NULL,
                                    [AlimamaID] [nvarchar](50) NULL,
                                    [Au_Name] [nvarchar](50) NULL,
                                    [BatchNumber] [nvarchar](50) NULL,
                                 CONSTRAINT [PK_Orders_1_{0}] PRIMARY KEY CLUSTERED 
                                (
	                                [Id] ASC
                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                ) ON [PRIMARY] 
        
                                CREATE TABLE ProductEffect_{0}(
                                [Id] [int] IDENTITY(1,1) NOT NULL,
	                            [CreateDate] [datetime] NULL,
	                            [PromotionBit] [nvarchar](50) NULL,
	                            [ClickNum] [int] NULL,
	                            [PayNum] [int] NULL,
	                            [Estimate] [money] NULL,
	                            [Income] [money] NULL,
	                            [PromotionType] [int] NULL,
	                            [LastDate] [datetime] NULL,
	                            [AlimamaID] [nvarchar](50) NULL,
	                            [UserName] [nvarchar](50) NULL,
                                 [Rate] [int] NULL,
                                [Rate_i] [int] NULL,
	                            [UserName_i] [nvarchar](50) NULL,
                                [Rate_ii] [int] NULL,
	                            [UserName_ii] [nvarchar](50) NULL,
                             CONSTRAINT [PK_ProductEffect_{0}] PRIMARY KEY CLUSTERED 
                            (
	                            [Id] ASC
                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]

                           ", promoter_id);
            return CONN_TopicSharing.Excute(Sql) > 0 ? true : false;
        }



       
        //public List<Orders> GetOrders(string trade_parent_id)
        //{
        //    string sql = string.Format(" select * from  Orders where trade_parent_id='{0}' ", trade_parent_id);

        //    List<Orders> Orders = CONN_TopicSharing.SQLQuery<Orders>(sql, null).ToList();

        //    return Orders;
        //}
      
        public List<Orders> ListPage(int promoter_id , string strWhere, string orderbys, int pagesize, int pageindex, out int count,object param=null)
        {
            List<Orders> Orderss = new List<Orders>();
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " 1=1 ";
            }

            if (string.IsNullOrEmpty(orderbys))
                orderbys = " Id desc ";

            Orderss = CONN_TopicSharing.SQLPager<Orders>(" OrderID,ProductID,ProductDes,CreateDate,PayPrice,EffectPrediction,AdvID,AdvName,ShopName,OrderStateDes ",string.Format( " Orders_{0} ",promoter_id), strWhere, "", "", orderbys, pagesize, pageindex, out count, param).ToList();

            return Orderss;
        }


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="dt"></param>
        public static void BulkToDB(DataTable dt,string tableName)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = dt.Rows.Count;

            try
            {
                sqlConn.Open();
               
                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="Str"></param>
        /// <returns></returns>
        public static int BulkDelte(string Str)
        {
           return DBHelper.ExecuteSql(Str);
        }


        /// <summary>
        /// 获取今日统计
        /// </summary>
        /// <param name="username"></param>
        /// <param name="promoter_id"></param>
        /// <returns></returns>
        public AgentCenterStatisticsDTO GetAgentTodayStatistics(string username, int promoter_id)
        {
            string tableName = "Orders_" + promoter_id;

            string sql = @" 
select count(1) ordernum ,sum(EffectPrediction) income,
(select sum(EffectPrediction) from  [dbo].[" + tableName + "] where  au_name=@username and orderstatedes='订单结算'  and CreateDate>=@CreateDate )settle from [dbo].[" + tableName + "] where au_name=@username and CreateDate>=@CreateDate ";
            AgentCenterStatisticsDTO model = new AgentCenterStatisticsDTO();


            model = CONN_TopicSharing.SQLQuery<AgentCenterStatisticsDTO>(sql, new { username = username, CreateDate = DateTime.Now.ToString("yyyy-MM-dd") }).FirstOrDefault();

            return model;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="promoter_id"></param>
        /// <returns></returns>
        public List<PromoterTodayStatistics> GetPromoterTodayStatistics(int promoter_id)
        {
            string sql =string.Format(@"select 'today' type, count(1) ordernumber,0 ClickNum,sum(EffectPrediction) estimate,0.00 income,
  (select count(1) from [dbo].[AgencyUsers] where promoter_id={0}) agentcount,
  0 usercount
   from [dbo].[Orders_{0}] where CreateDate>'{1}'
 union all
   select 'yestoday' type,sum(paynum) ordernumber,sum(ClickNum)ClickNum ,sum(estimate)estimate,sum(income)income ,
   0 agentcount, 0 usercount
 
   from [dbo].[ProductEffect_{0}]  where CreateDate='{2}'", promoter_id,DateTime.Now.ToString("yyyy-MM-dd"),DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"));
            
             var list=new List<PromoterTodayStatistics>();
             list = CONN_TopicSharing.SQLQuery<PromoterTodayStatistics>(sql, null).ToList();
             return list;
 
        }



    }
}
